﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Web;
using System.Web.UI.WebControls;

namespace InhollandAir.auxiliary
{
    public class Sql
    {
        // The SQL connections
        private static readonly SqlConnection Conn = new SqlConnection();

        /// <summary>
        /// Gets the complete list of countries in database
        /// </summary>
        /// <returns>List of Counrty objects</returns>
        public static List<Country> GetCountries()
        {
            // Countries not loaded yet, create query and load them
            var countries = new List<Country>();
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightSelect"].ConnectionString;

                // DB query
                const string query = "SELECT id, name FROM [dbo].[Country]";
                var command = new SqlCommand(query, Conn);

                // Open connection
                Conn.Open();

                // Execute query
                using (var reader = command.ExecuteReader())
                {
                    // Get all objects
                    while (reader.Read())
                    {
                        // Create country object
                        var country = Country.CreateCountryWith(reader.GetInt32(reader.GetOrdinal("id")),
                                                                reader.GetString(reader.GetOrdinal("name")));
                        // Add country as response
                        countries.Add(country);
                    }

                    // Close reader
                    reader.Close();
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get countries from dbo.Country", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get countries from dbo.Country", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            // Return the countries
            return countries;
        }

        public static Country GetCountryWithId(int id)
        {
            // Countries not loaded yet, create query and load them
            Country country = null;
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightSelect"].ConnectionString;

                // DB query
                const string query = "SELECT id, name FROM [dbo].[Country] WHERE id = @id";
                var command = new SqlCommand(query, Conn);
                command.Parameters.Add("@id", id);

                // Open connection
                Conn.Open();

                // Execute query
                using (var reader = command.ExecuteReader())
                {
                    // Get all objects
                    if (reader.Read())
                    {
                        // Create country object
                        var newCountry = Country.CreateCountryWith(reader.GetInt32(reader.GetOrdinal("id")),
                                                                reader.GetString(reader.GetOrdinal("name")));
                        // Add country as response
                        country = newCountry;
                    }

                    // Close reader
                    reader.Close();
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get countries by id from dbo.Country", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get countries by id from dbo.Country", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            // Return the countries
            return country;
        }

        public static List<Airplane> GetAirplanes()
        {
            // Countries not loaded yet, create query and load them
            var airplanes = new List<Airplane>();
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["GetPlanes"].ConnectionString;

                // DB query
                const string query = "SELECT id, name, capacity FROM [dbo].[Plane]";
                var command = new SqlCommand(query, Conn);
                
                // Open connection
                Conn.Open();

                // Execute query
                using (var reader = command.ExecuteReader())
                {
                    // Get all objects
                    while (reader.Read())
                    {
                        var plane = new Airplane { Id = int.Parse(reader["id"].ToString()), Capacity = int.Parse(reader["capacity"].ToString()), Name = reader["name"].ToString() };
                        airplanes.Add(plane);
                    }

                    // Close reader
                    reader.Close();
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get airplanes from dbo.Plane", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get airplaines from dbo.Plane", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            // Return the countries
            return airplanes;
        }

        public static Airplane GetAirplaneWithId(int id)
        {
            // Countries not loaded yet, create query and load them
            Airplane airplane = null;
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["GetPlanes"].ConnectionString;

                // DB query
                const string query = "SELECT id, name, capacity, price FROM [dbo].[Plane] WHERE id = @id";
                var command = new SqlCommand(query, Conn);
                command.Parameters.AddWithValue("@id", id);

                // Open connection
                Conn.Open();

                // Execute query
                using (var reader = command.ExecuteReader())
                {
                    // Get all objects
                    if (reader.Read())
                    {
                        var plane = new Airplane { Id = int.Parse(reader["id"].ToString()), Capacity = int.Parse(reader["capacity"].ToString()), Name = reader["name"].ToString(), Price = double.Parse(reader["price"].ToString()) };
                        airplane = plane;
                    }

                    // Close reader
                    reader.Close();
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get airplane by id from dbo.Plane", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get airplane by id from dbo.Plane", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            // Return the countries
            return airplane;
        }

        /// <summary>
        /// All airports fot the given countryId
        /// </summary>
        /// <param name="countryId">The id of the country</param>
        /// <returns>List of Airport objects</returns>
        public static List<Airport> GetAirportsForCountry(int countryId)
        {
            // Create response list
            var airports = new List<Airport>();

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightSelect"].ConnectionString;

                // Create query
                const string query = "SELECT id, name FROM [dbo].[Airport] WHERE country_id = @id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@id", countryId);

                // Open connection
                Conn.Open();

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                {
                    // Read all data
                    while (reader.Read())
                    {
                        // Create airport object
                        var airport = Airport.CreateAirportWith(reader.GetInt32(reader.GetOrdinal("id")),
                                                                reader.GetString(reader.GetOrdinal("name")));
                        // Add airport to response
                        airports.Add(airport);
                    }
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get airport by country from dbo.Airport", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get airport by country from dbo.Airport", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            // Return all the aiports for the country
            return airports;
        }

        /// <summary>
        /// Returns all the routes from the "From" airport to the "To" airport
        /// </summary>
        /// <param name="from">The Airport object where the route must start</param>
        /// <param name="to">The Airport object where the route must end</param>
        /// <returns>All the routes between the airports (from -> to)</returns>
        public static List<Route> GetRoutesWithAirports(Airport from, Airport to)
        {
            // Create response
            var routes = new List<Route>();

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightSelect"].ConnectionString;

                // Create query
                const string query =
                    "SELECT r.id as id FROM [dbo].[Route] as r, [dbo].[Airport] as a, [dbo].[Airport] as a2 WHERE r.airport_from = @from AND r.airport_to = @to AND r.airport_from = a.id AND r.airport_to = a2.id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@from", from.Id);
                myCommand.Parameters.AddWithValue("@to", to.Id);

                // Open the connection
                Conn.Open();

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                {
                    // Loop through all fetched objects
                    while (reader.Read())
                    {
                        // Create route
                        var route = Route.CreateRouteWith(reader.GetInt32(reader.GetOrdinal("id")), from, to);
                        // Add route to response
                        routes.Add(route);
                    }
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get route by airport from dbo.Airport and dbo.Route", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get route by airport from dbo.Airport and dbo.Route", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            // Return the routes
            return routes;
        }

        /// <summary>
        /// Get Airport object with the given ID
        /// </summary>
        /// <param name="id">The database ID of the airport</param>
        /// <returns>The airport for the given ID</returns>
        public static Airport GetAirportWithId(int id)
        {
            // Create response
            Airport airport = null;

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightSelect"].ConnectionString;

                // Create query
                const string query =
                    "SELECT TOP 1 a.id as id, a.name as name, a.short_name as shortname, c.id as country_id, c.name as countryname FROM [dbo].[Airport] as a, [dbo].[Country] as c WHERE a.id = @id and c.id = a.country_id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@id", id);

                // Open connection
                Conn.Open();

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                {
                    // Loop through all fetched objects
                    if (reader.Read())
                    {
                        // Create country
                        var country = Country.CreateCountryWith(reader.GetInt32(reader.GetOrdinal("country_id")), reader.GetString(reader.GetOrdinal("countryname")));

                        // Create airport
                        airport = Airport.CreateAirportWith(reader.GetInt32(reader.GetOrdinal("id")),
                                                            reader.GetString(reader.GetOrdinal("name")),
                                                            reader.GetString(reader.GetOrdinal("shortname")), 
                                                            country);
                    }
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get airport by id from dbo.Airport and dbo.Country", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get airport by id from dbo.Airport and dbo.Country", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            // Return the airport
            return airport;
        }

        /// <summary>
        /// Get all the flights with the given route and startdate
        /// </summary>
        /// <param name="route">The route of the flight</param>
        /// <param name="startdate">The minimum startdate</param>
        /// <returns></returns>
        public static List<Flight> GetFlightsForRouteWithStartDate(Route route, DateTime startdate)
        {
            // Create response
            var flights = new List<Flight>();

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightSelect"].ConnectionString;

                // Create query
                const string query =
                    "SELECT id, startdate FROM [dbo].[Flight] WHERE route_id = @id AND startdate >= @startdate";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@id", route.Id);
                myCommand.Parameters.AddWithValue("@startdate", startdate.ToString("yyyy-MM-dd HH:mm:ss"));

                // Open connection
                Conn.Open();

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                {
                    // Loop through all fetched objects
                    while (reader.Read())
                    {
                        // Create flight
                        var flight = Flight.CreateFlightWith(reader.GetInt32(reader.GetOrdinal("id")), route,
                                                             reader.GetDateTime(reader.GetOrdinal("startdate")));
                        // Add flight to response
                        flights.Add(flight);
                    }
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get flights for route by startdate dbo.Flight", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get flights for route by startdate dbo.Flight", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            // Return flights
            return flights;
        }

        /// <summary>
        /// All car makes available for rent
        /// </summary>
        /// <returns>List of ListItem objects</returns>
        public static List<ListItem> GetAllCarMakes()
        {
            var cars = new List<ListItem>();

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["TripSelect"].ConnectionString;

                Conn.Open();
                const string query =
                    "SELECT * FROM [InhollandAir].[dbo].[Car]";
                var myCommand = new SqlCommand(query, Conn);

                using (var reader = myCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var car = new ListItem(reader["Make"].ToString(), reader["id"].ToString());
                        cars.Add(car);
                    }
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get all cars from dbo.Car", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get all cars from dbo.Car", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }

            return cars;
        }

        /// <summary>
        /// Get specifications for a specific car (type and capacity) by id
        /// </summary>
        /// <param name="id">The id of the car make</param>
        /// <returns>Dictionary with car type and capacity specified</returns>
        public static Dictionary<string, string> GetCarSpecifications(string id)
        {
            var carSpecifications = new Dictionary<string, string>();

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["TripSelect"].ConnectionString;

                Conn.Open();
                const string query =
                    "SELECT * FROM [InhollandAir].[dbo].[Car] WHERE id = @id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@id", id);

                using (var reader = myCommand.ExecuteReader())
                    if (reader.Read())
                        carSpecifications.Add("Specifications", reader["Type"]+ ":"+ reader["Capacity"]);
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get car specifications from dbo.Car", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get car specifications from dbo.Car", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }

            return carSpecifications;
        }

        /// <summary>
        /// All hotels available for booking
        /// </summary>
        /// <returns>List of ListItem objects</returns>
        public static List<ListItem> GetHotels()
        {
            var hotelCountries = new List<ListItem>();
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["TripSelect"].ConnectionString;

                Conn.Open();
                const string query =
                    "SELECT * FROM [InhollandAir].[dbo].[Hotels]";
                var myCommand = new SqlCommand(query, Conn);

                using (var reader = myCommand.ExecuteReader())
                    while (reader.Read())
                        hotelCountries.Add(new ListItem(reader["Name"] + ", " + reader["City"] + ", " + reader["Country"], reader["id"].ToString()));
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get all hotels from dbo.Hotels", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get all hotels from dbo.Hotels", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return hotelCountries;
        }

        /// <summary>
        /// All countries availble to select in the account options
        /// </summary>
        /// <returns>List of Airport objects</returns>
        public static List<ListItem> GetAllCountriesInWorld()
        {
            var countries = new List<ListItem>();

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["AllCountrySelect"].ConnectionString;

                Conn.Open();
                var myCommand = new SqlCommand("SELECT * FROM [InhollandAir].[dbo].[AllCountry]", Conn);

                using (var reader = myCommand.ExecuteReader())
                    while (reader.Read())
                        countries.Add(new ListItem(reader["name"].ToString(), reader["id"].ToString()));
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get all countries from dbo.AllCountry", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get all countries from dbo.AllCountry", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }

            return countries;
        }

        /// <summary>
        /// Check if user already exists to make sure no duplicates are created
        /// </summary>
        /// <param name="username">The username to check for existance</param>
        /// <returns>String with count or error message</returns>
        public static string CheckIfUserExists(string username)
        {
            string exists = "0";
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["UserSelect"].ConnectionString;

                Conn.Open();
                const string query = "SELECT COUNT(*) FROM [InhollandAir].[dbo].[User] WHERE username = @username";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@username", username);

                var count = (int) myCommand.ExecuteScalar();
                exists =  count.ToString(CultureInfo.InvariantCulture);
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Check user existance at dbo.User", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Check user existance at dbo.User", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return exists;
        }

        /// <summary>
        /// Add user in the user table, use the hashed password
        /// Add salt and iteration info in salt table using the returned user id
        /// </summary>
        /// <param name="username">The username to register</param>
        /// /// <param name="password">The to hash</param>
        /// <returns>String with the user id or error message</returns>
        public static string CreateNewUser(string username, string password)
        {
            string uid;
            var encryptedValues = HashComparison.GenerateRandomPasswordAndHash(password);
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["NewCustomerInsert"].ConnectionString;

                Conn.Open();
                string query = "INSERT INTO [InhollandAir].[dbo].[User] VALUES (@username, @password, @lastlogin)";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@username", username);
                myCommand.Parameters.AddWithValue("@password", encryptedValues["hash"]);
                myCommand.Parameters.AddWithValue("@lastlogin", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                myCommand.ExecuteNonQuery();

                myCommand.Parameters.Clear();
                myCommand.CommandText = "SELECT @@IDENTITY";

                uid = myCommand.ExecuteScalar().ToString();
                myCommand.Parameters.Clear();

                query = "INSERT INTO [InhollandAir].[dbo].[Salt] VALUES (@user_id, @salt, @iteration)";
                myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@user_id", uid);
                myCommand.Parameters.AddWithValue("@salt", encryptedValues["salt"]);
                myCommand.Parameters.AddWithValue("@iteration", encryptedValues["iteration"]);

                myCommand.ExecuteNonQuery();

                myCommand.Parameters.Clear();

                query = "INSERT INTO [InhollandAir].[dbo].[Miles] VALUES (@user_id, @balance, @lasttransaction)";
                myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@user_id", uid);
                myCommand.Parameters.AddWithValue("@balance", 300); //Give each new user 300 IHM
                myCommand.Parameters.AddWithValue("@lasttransaction", 0);

                myCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                uid = "Could not create user.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Creating new user at dbo.User, dbo.User, dbo.Miles", ex.Message, true);
            }
            catch (Exception ex)
            {
                uid = "Could not create user.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Creating new user at dbo.User, dbo.User, dbo.Miles", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return uid;
        }

        public static string CreateNewPerson(int userId, string username, string firstname, string familyname, string birthdate, 
            string sex, string phonenumber, string email, string country, string postalcode, string number)
        {
            string uid;
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["NewCustomerInsert"].ConnectionString;

                Conn.Open();
                const string query = "INSERT INTO [InhollandAir].[dbo].[Person] VALUES (@userid, @firstname, @familyname, @birthdate, @sex, @phonenumber, @email, @country, @postalcode, @number)";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@userid", userId);
                myCommand.Parameters.AddWithValue("@firstname", firstname);
                myCommand.Parameters.AddWithValue("@familyname", familyname);
                myCommand.Parameters.AddWithValue("@birthdate", birthdate);
                myCommand.Parameters.AddWithValue("@sex", sex);
                myCommand.Parameters.AddWithValue("@phonenumber", phonenumber);
                myCommand.Parameters.AddWithValue("@email", email);
                myCommand.Parameters.AddWithValue("@country", country);
                myCommand.Parameters.AddWithValue("@postalcode", postalcode);
                myCommand.Parameters.AddWithValue("@number", number);

                myCommand.ExecuteNonQuery();

                myCommand.Parameters.Clear();
                myCommand.CommandText = "SELECT @@IDENTITY";

                uid = myCommand.ExecuteScalar().ToString();

            }
            catch (SqlException ex)
            {
                uid = "Could not create user.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Creating new user at dbo.Person", ex.Message, true);
            }
            catch (Exception ex)
            {
                uid = "Could not create user.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Creating new user at dbo.Person", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return uid;
        }

        public static Customer GetUserWithCredentials(string username, string password)
        {
            Customer customer = null;
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["UserSelect"].ConnectionString;

                // Create query
                const string query =
                    "SELECT id FROM [dbo].[User] WHERE username = @username AND password = @password";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@username", username);
                myCommand.Parameters.AddWithValue("@password", HashComparison.CheckHashMatch(password, username));

                // Open connection
                Conn.Open();

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                {
                    // Loop through all fetched objects
                    if (reader.Read())
                    {
                        // Create customer
                        customer = Customer.CreateCustomerWith(reader.GetInt32(reader.GetOrdinal("id")), username);
                    }
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get and match credentials at dbo.User", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get and match credentials at dbo.User", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            return customer;
        }

        public static Customer GetUserWithUsername(string username)
        {
            Customer customer = null;
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["UserSelect"].ConnectionString;

                // Create query
                const string query =
                    "SELECT id FROM [dbo].[User] WHERE username = @username";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@username", username);
                
                // Open connection
                Conn.Open();

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                {
                    // Loop through all fetched objects
                    if (reader.Read())
                    {
                        // Create customer
                        customer = Customer.CreateCustomerWith(reader.GetInt32(reader.GetOrdinal("id")), username);
                    }
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get userid by username at dbo.User", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get userid by username at dbo.User", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            return customer;
        }

        public static string[] GetSaltAndIterationByUserId(string username)
        {
            var vals = new string[2];
            Customer userid = GetUserWithUsername(username);
            try
            {
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["GetSaltInfo"].ConnectionString;

                // Create query
                const string query =
                    "SELECT salt, iteration FROM [dbo].[Salt] WHERE user_id = @user_id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@user_id", userid.Id.ToString(CultureInfo.InvariantCulture));

                // Open connection
                Conn.Open();

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                {
                    // Loop through all fetched objects
                    if (reader.Read())
                    {
                        vals[0] = reader["iteration"].ToString();
                        vals[1] = reader["salt"].ToString();
                    }
                }
            }
            catch (SqlException ex)
            {
                vals[0] = "The database is having some problems right now, please try again later.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get salt and iteration at dbo.Salt", ex.Message, true);
            }
            catch (Exception ex)
            {
                vals[0] = "The application could not complete your request.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get salt and iteration at dbo.Salt", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }

            return vals;
        }

        public static string GetHashedPasswordByUsername(string username)
        {
            string hash = string.Empty;
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["UserSelect"].ConnectionString;

                Conn.Open();
                const string query = "SELECT password FROM [InhollandAir].[dbo].[User] WHERE username = @username";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@username", username);

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                    // Loop through all fetched objects
                    if (reader.Read())
                        hash = reader["password"].ToString();
            }
            catch (SqlException ex)
            {
                hash = "The database is having some problems right now, please try again later.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get hashed password by username at dbo.User", ex.Message, true);
            }
            catch (Exception ex)
            {
                hash = "The application could not complete your request.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get hashed password by username at dbo.User", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return hash;
        }

        public static Dictionary<string, string> GetPersonDetails(int uid)
        {
            var details = new Dictionary<string, string>();

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["GetAccountInfo"].ConnectionString;

                Conn.Open();
                string query = "SELECT * FROM [InhollandAir].[dbo].[Person] WHERE user_id = @user_id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@user_id", uid);

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                    // Loop through all fetched objects
                    if (reader.Read())
                    {
                        details.Add("firstname", reader["firstname"].ToString());
                        details.Add("familyname", reader["familyname"].ToString());
                        details.Add("birthdate", reader["birthdate"].ToString());
                        details.Add("sex", reader["sex"].ToString());
                        details.Add("phonenumber", reader["phonenumber"].ToString());
                        details.Add("emailaddress", reader["emailaddress"].ToString());
                        details.Add("country", reader["country"].ToString());
                        details.Add("postalcode", reader["postalcode"].ToString());
                        details.Add("number", reader["number"].ToString());
                    }

                myCommand.Parameters.Clear();
                query = "SELECT * FROM [InhollandAir].[dbo].[Miles] WHERE user_id = @user_id";
                myCommand.CommandText = query;
                myCommand.Parameters.AddWithValue("@user_id", uid);

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                    // Loop through all fetched objects
                    if (reader.Read())
                    {
                        details.Add("balance", reader["balance"].ToString());
                        details.Add("lasttransaction", reader["lasttransaction"].ToString());
                    }

            }
            catch (SqlException ex)
            {
                details.Add("SqlException", "The database is having some problems right now, please try again later.");
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get person details for account page at dbo.Person, dbo.Miles", ex.Message, true);
            }
            catch (Exception ex)
            {
                details.Add("Exception", "The application could not complete your request.");
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get person details for account page at dbo.Person, dbo.Miles", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }

            return details;
        }

        public static string UpdatePersonalDetails(int userId, string phone, string email, string country, string postal, string nr)
        {
            string status = string.Empty;

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["UpdatePersonalDetails"].ConnectionString;

                Conn.Open();
                const string query = "UPDATE [InhollandAir].[dbo].[Person] SET phonenumber = @phonenumber, emailaddress = @emailaddress, country = @country, postalcode = @postalcode, number = @housenumber WHERE user_id = @user_id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@user_id", userId);
                myCommand.Parameters.AddWithValue("@phonenumber", phone);
                myCommand.Parameters.AddWithValue("@emailaddress", email);
                myCommand.Parameters.AddWithValue("@country", country);
                myCommand.Parameters.AddWithValue("@postalcode", postal);
                myCommand.Parameters.AddWithValue("@housenumber", nr);

                if (myCommand.ExecuteNonQuery() > 0) status = "Details successfully updated!";

            }
            catch (SqlException ex)
            {
                status = "The database is having some problems right now, please try again later.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Update person details for account page at dbo.Person, dbo.Miles", ex.Message, true);
            }
            catch (Exception ex)
            {
                status = "The application could not complete your request.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Update person details for account page at dbo.Person, dbo.Miles", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }

            return status;
        }

        public static string UpdatePasswordDetails(string username, string password, int uid)
        {
            string status = string.Empty;
            var encryptedValues = HashComparison.GenerateRandomPasswordAndHash(password);

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["UpdatePassword"].ConnectionString;

                Conn.Open();
                string query = "UPDATE [InhollandAir].[dbo].[User] SET password=@password WHERE id=@id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@id", uid);
                myCommand.Parameters.AddWithValue("@password", encryptedValues["hash"]);

                myCommand.ExecuteNonQuery();
                myCommand.Parameters.Clear();

                query = "UPDATE [InhollandAir].[dbo].[Salt] SET salt=@salt, iteration=@iteration WHERE user_id=@user_id";
                myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@user_id", uid);
                myCommand.Parameters.AddWithValue("@salt", encryptedValues["salt"]);
                myCommand.Parameters.AddWithValue("@iteration", encryptedValues["iteration"]);

                status = myCommand.ExecuteNonQuery() > 0 ? "Password successfully updated!" : "Could not update password!";

            }
            catch (SqlException ex)
            {
                status = "The database is having some problems right now, please try again later.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Update password at dbo.User, dbo.Salt", ex.Message, true);
            }
            catch (Exception ex)
            {
                status = "The application could not complete your request.";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Update password at dbo.User, dbo.Salt", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }

            return status;
        }

        public static int[] GetMilesById(int uid)
        {
            var balance = new int[2];
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["GetAccountInfo"].ConnectionString;

                Conn.Open();
                const string query = "SELECT * FROM [InhollandAir].[dbo].[Miles] WHERE user_id = @user_id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@user_id", uid);

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                    // Loop through all fetched objects
                    if (reader.Read())
                    {
                        balance[0] = int.Parse(reader["balance"].ToString());
                        balance[1] = int.Parse(reader["lasttransaction"].ToString());
                    }

            }
            catch (SqlException ex)
            {
                balance[0] = -1;
                balance[1] = -1;
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get Miles count at dbo.Miles", ex.Message, true);
            }
            catch (Exception ex)
            {
                balance[0] = -2;
                balance[1] = -2;
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get Miles count at dbo.Miles", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return balance;
        }

        internal static bool InsertCharterFlightWith(Customer customer, Airplane airplane, Airport fromAirport, Airport toAirport, DateTime startDate)
        {
            if (customer == null) throw new ArgumentNullException("customer");
            if (airplane == null) throw new ArgumentNullException("airplane");
            if (fromAirport == null) throw new ArgumentNullException("fromAirport");
            if (toAirport == null) throw new ArgumentNullException("toAirport");

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["InsertCharter"].ConnectionString;

                // DB query
                const string query = "INSERT INTO [dbo].[Charter] (user_id, airport_from, airport_to, price, plane_id,startdate) VALUES (@userId,@fromId,@toId,@price, @airplaneId, @dateTime)";
                var command = new SqlCommand(query, Conn);

                command.Parameters.AddWithValue("@userId", customer.Id);
                command.Parameters.AddWithValue("@fromId", fromAirport.Id);
                command.Parameters.AddWithValue("@toId", toAirport.Id);
                command.Parameters.AddWithValue("@price", airplane.Price);
                command.Parameters.AddWithValue("@airplaneId", airplane.Id);
                command.Parameters.AddWithValue("@dateTime", startDate);

                // Open connection
                Conn.Open();

                // Execute query
                var reader = command.ExecuteNonQuery();
                if(reader <= 0 ) return false;   
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Insert charter flight at dbo.Charter", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Insert charter flight at dbo.Charter", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            return true;
        }

        internal static Flight GetFlightWithId(int id)
        {
            Flight flight = null;

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightSelect"].ConnectionString;

                // DB query
                const string query = "SELECT id, startdate FROM [dbo].[Flight] WHERE id = @id";
                var command = new SqlCommand(query, Conn);

                command.Parameters.AddWithValue("@id", id);

                // Open connection
                Conn.Open();

                // Execute query
                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        flight = new Flight
                        {
                            Id = Convert.ToInt16(reader["id"]),
                            Route = null,
                            StartDate = Convert.ToDateTime(reader["startdate"])
                        };
                    }
                }
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get flight by id at dbo.Flight", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get flight by id at dbo.Flight", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }

            return flight;
        }

        public static int[] GetBookingStatusById(int bid)
        {
            var booking = new int[2];
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["BookingHandler"].ConnectionString;

                Conn.Open();
                const string query = "SELECT user_id, status FROM [InhollandAir].[dbo].[Booking] WHERE id = @id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@id", bid);

                // Loop through all data
                using (var reader = myCommand.ExecuteReader())
                    // Loop through all fetched objects
                    if (reader.Read())
                    {
                        booking[0] = int.Parse(reader["user_id"].ToString());
                        booking[1] = int.Parse(reader["status"].ToString());
                    }

            }
            catch (SqlException ex)
            {
                booking[0] = -1;
                booking[1] = -1;
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get booking status by id dbo.Booking", ex.Message, true);
            }
            catch (Exception ex)
            {
                booking[0] = -2;
                booking[1] = -2;
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get booking status by id dbo.Booking", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }

            return booking;
        }

        public static int GetPriceForFlight(int id)
        {
            int price = 99999;

            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightSelect"].ConnectionString;

                // DB query
                const string query = "SELECT price FROM [dbo].[Flight] WHERE id = @id";
                var command = new SqlCommand(query, Conn);

                command.Parameters.AddWithValue("@id", id);

                // Open connection
                Conn.Open();

                // Execute query
                using (var reader = command.ExecuteReader())
                    if (reader.Read())
                        price = (int) reader["price"];
            }
            catch (SqlException ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get price in euro for a flight at dbo.Flight", ex.Message, true);
            }
            catch (Exception ex)
            {
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get price in euro for a flight at dbo.Flight", ex.Message, true);
            }
            finally
            {
                // Close connection
                Conn.Close();
            }
            return price;
        }

        public static string UpdateBookingStatus(int id, int status)
        {
            string booking;
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["BookingHandler"].ConnectionString;

                Conn.Open();
                const string query = "UPDATE [InhollandAir].[dbo].[Booking] SET status=@status WHERE id = @id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@id", id);
                myCommand.Parameters.AddWithValue("@status", status);

                booking = myCommand.ExecuteNonQuery().ToString(CultureInfo.InvariantCulture);
            }
            catch (SqlException ex)
            {
                booking = "We're having database problems, try again later";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Update booking status at dbo.Booking", ex.Message, true);
            }
            catch (Exception ex)
            {
                booking = "We're having server problems, try again later";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Update booking status at dbo.Booking", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return booking;
        }

        public static string UpdateMilesAmount(int uid, int amount, bool add)
        {
            string miles;
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["GetAccountInfo"].ConnectionString;

                Conn.Open();
                string query = add 
                                   ? "UPDATE [InhollandAir].[dbo].[Miles] SET balance=balance+@amount, lasttransaction=@amount WHERE user_id = @user_id" 
                                   : "UPDATE [InhollandAir].[dbo].[Miles] SET balance=balance-@amount, lasttransaction=-@amount WHERE user_id = @user_id";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@user_id", uid);
                myCommand.Parameters.AddWithValue("@amount", amount);

                miles = myCommand.ExecuteNonQuery().ToString(CultureInfo.InvariantCulture);
            }
            catch (SqlException ex)
            {
                miles = "We are having database problems, try again later";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Update miles balance at dbo.Miles", ex.Message, true);
            }
            catch (Exception ex)
            {
                miles = "We are having server problems, try again later";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Update miles balance at dbo.Miles", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return miles;
        }

        public static DataSet GetFullBookingOverviewByUserId(int uid)
        {
            var booking = new DataSet();
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["BookingHandler"].ConnectionString;

                Conn.Open();
                const string query = "SELECT [InhollandAir].[dbo].[Booking].id ,[InhollandAir].[dbo].[Booking].amount_customer, [InhollandAir].[dbo].[Booking].status, " +
                    "[InhollandAir].[dbo].[Flight].startdate, [InhollandAir].[dbo].[Airport].short_name " +
                    "FROM [InhollandAir].[dbo].[Booking] " +
                    "INNER JOIN [InhollandAir].[dbo].[Flight] " +
                    "ON [InhollandAir].[dbo].[Booking].flight_id=[InhollandAir].[dbo].[Flight].id " +
                    "INNER JOIN [InhollandAir].[dbo].[Route] " +
                    "ON [InhollandAir].[dbo].[Flight].route_id=[InhollandAir].[dbo].[Route].id " +
                    "INNER JOIN [InhollandAir].[dbo].[Airport] " +
                    "ON [InhollandAir].[dbo].[Route].airport_from=[InhollandAir].[dbo].[Airport].id " +
                    "OR [InhollandAir].[dbo].[Route].airport_to=[InhollandAir].[dbo].[Airport].id " +
                    "WHERE [InhollandAir].[dbo].[Booking].status=2 AND [InhollandAir].[dbo].[Booking].user_id = @user_id";
                var command = new SqlCommand(query, Conn);
                command.Parameters.AddWithValue("@user_id", uid);

                var adapter = new SqlDataAdapter(command);

                adapter.Fill(booking);
            }
            catch (SqlException ex)
            {
                booking.Tables.Add("Error").Columns.Add("Error");
                DataRow dr = booking.Tables["Error"].NewRow();
                dr["Error"] = "Oops we had a database error.";
                booking.Tables["Error"].Rows.Add(dr);
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get booking overview at dbo.Booking, dbo.Flight, dbo.Airport, dbo.Route", ex.Message, true);
            }
            catch (Exception ex)
            {
                booking.Tables.Add("Error").Columns.Add("Error");
                DataRow dr = booking.Tables["Error"].NewRow();
                dr["Error"] = "Oops we had a server error.";
                booking.Tables["Error"].Rows.Add(dr);
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Get booking overview at dbo.Booking, dbo.Flight, dbo.Airport, dbo.Route", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return booking;
        }

        public static string CancelBookingById(string id, int uid, int status)
        {
            string booking;
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["BookingHandler"].ConnectionString;

                Conn.Open();
                const string query = "UPDATE [InhollandAir].[dbo].[Booking] SET status=@status WHERE id = @id AND user_id = @uid";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@id", id);
                myCommand.Parameters.AddWithValue("@uid", uid);
                myCommand.Parameters.AddWithValue("@status", status);

                booking = myCommand.ExecuteNonQuery().ToString(CultureInfo.InvariantCulture);
            }
            catch (SqlException ex)
            {
                booking = "We're having database problems, try again later";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Update booking to canceled (-1) at dbo.Booking", ex.Message, true);
            }
            catch (Exception ex)
            {
                booking = "We're having server problems, try again later";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Update booking to canceled (-1) at dbo.Booking", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return booking;
        }

        public static string InsertBooking(int uid, int fid, int qt, int status)
        {
            string booking;
            try
            {
                // Set correct connection string
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["BookingHandler"].ConnectionString;

                Conn.Open();
                const string query = "INSERT INTO [InhollandAir].[dbo].[Booking] VALUES (@user_id, @flight_id, @amount_customer, @status)";
                var myCommand = new SqlCommand(query, Conn);
                myCommand.Parameters.AddWithValue("@user_id", uid);
                myCommand.Parameters.AddWithValue("@flight_id", fid);
                myCommand.Parameters.AddWithValue("@amount_customer", qt);
                myCommand.Parameters.AddWithValue("@status", status);

                myCommand.ExecuteNonQuery();

                myCommand.Parameters.Clear();
                myCommand.CommandText = "SELECT @@IDENTITY";

                booking = myCommand.ExecuteScalar().ToString();
            }
            catch (SqlException ex)
            {
                booking = "We're having database problems, try again later";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Could not create booking at dbo.Booking", ex.Message, true);
            }
            catch (Exception ex)
            {
                booking = "We're having server problems, try again later";
                Logging.LogEvent(0, HttpContext.Current.Request.Url, "Could not create booking at dbo.Booking", ex.Message, true);
            }
            finally
            {
                Conn.Close();
            }
            return booking;
        }
       
    }
}